package com.fzrskj.cloud.free.user.dao;
/**
 * @ClassName: UserChargeDao
 * @Description: 浣欓琛?
 * @author 832
 * @date 2016骞?10鏈?11鏃?
 */

import com.fzrskj.cloud.free.core.utils.CloudContextUtils;
import com.fzrskj.cloud.free.core.utils.CloudDbDrvUtils;
import com.fzrskj.cloud.free.user.bean.UserCharge;
import com.fzrskj.cloud.free.user.bean.statistics.Statistics;
import com.fzrskj.cloud.free.user.tool.StatistisTool;
import ms.core.consts.SysErr;
import ms.core.db.IDbDrv;
import ms.core.object.CObject;
import ms.core.tool.StrTool;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class UserChargeDao extends CObject {
    //private declarations
    private IDbDrv db = null;

    //constructor function to initialize database access object
    public UserChargeDao() {
        db = CloudDbDrvUtils.getDbUtilsDrv();
    }

    //to free resource
    @Override
    public void dispose() {
        db.closeConnection();
    }

    /**
     * 获取消费记录
     *
     * @param userName
     * @param page
     * @param pageSize
     * @return
     */
    public List<UserCharge> getUserCharge(String userName, int page, int pageSize, String startTime, String endDate, String orderId) {
        //将用户名分开可查询个人和全部
        String sql = "select * from user_charge where 1=1 ";

        sql += " and agentId='" + CloudContextUtils.getAgentId() + "'";

        if (!StrTool.isBlankStr(userName)) {
            sql += " and userName='" + userName + "'";
        }
        //如果开始时间不等于0  查询的时间大于开始时间
        if (!StrTool.isBlankStr(startTime)) {
            sql += " and '" + startTime + "' <=" + "regTime";
        }
        if (!StrTool.isBlankStr(endDate)) {
            sql += " and regTime <='" + endDate + "'";
        }
        if (!StrTool.isBlankStr(orderId)) {
            sql += " and orderId='" + orderId + "'";
        }
        if (page > 0 && pageSize > 0) {
            sql += " order by regTime desc";
            sql += " limit " + (page - 1) * pageSize + "," + pageSize;
        }
        List<UserCharge> getUserCharge = db.queryForBeans(sql, UserCharge.class);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return getUserCharge;

    }

    /**
     * 添加余额记录
     *
     * @param userCharge
     * @return
     */
    public int addUserCharge(UserCharge userCharge) {
        userCharge.setAgentId(CloudContextUtils.getAgentId());
        String sql = "insert into user_charge(amount,bankCode,orderId,regTime,status," +
                "userId,userName,remark, bizOrderId, agentId, fromAgentId)" +
                "values(?,?,?,?,?," +
                "?,?,?,?,?,?);";
        Object[] prm = {userCharge.getAmount(), userCharge.getBankCode(), userCharge.getOrderId(), userCharge.getRegTime(), userCharge.getStatus(),
                userCharge.getUserId(), userCharge.getUserName(), userCharge.getRemark(), userCharge.getBizOrderId(), userCharge.getAgentId(), userCharge.getFromAgentUserId()};
        int ret = db.executeSql(sql, prm);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    /**
     * 查询总数
     *
     * @param userName
     * @param startTime
     * @param endDate
     * @param orderId
     * @return
     */
    public int UserChargePage(String userName, String startTime, String endDate, String orderId) {
        List<Object> list = new ArrayList<>();
        //将用户名分开可查询个人和全部
        String sql = "select count(id) from user_charge where 1=1 and agentId=?";
        list.add(CloudContextUtils.getAgentId());
        if (!StrTool.isBlankStr(userName)) {
            sql += " and userName = ?";
            list.add(userName);
        }
        //如果开始时间不等于0  查询的时间大于开始时间
        if (!StrTool.isBlankStr(startTime)) {
            sql += " and regTime >= ?";
            list.add(startTime);
        }
        if (!StrTool.isBlankStr(endDate)) {
            sql += " and regTime <= ?";
            list.add(endDate);
        }
        if (!StrTool.isBlankStr(orderId)) {
            sql += " and orderId=?";
            list.add(orderId);
        }
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        long counts = (Long) db.queryFieldValue(sql, Long.class, params, 1);
        int count = (int) counts;
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return count;
    }

    /**
     * 根据用户名获得消费总金额
     *
     * @param uname
     * @return
     */
    public double getAllAmount(String uname) {
        Object params[] = {uname, CloudContextUtils.getAgentId(), uname, CloudContextUtils.getAgentId()};
        String sql = "select (IFNULL(sum(a.amount),0)-b.price)result from  user_charge a ,"
                + "(select IFNULL(sum(c.price),0) as price from user_invoice c where c.userName=? and c.agentId=?)b where a.userName=? and a.agentId=?";
        Double ret = (Double) db.queryFieldValue(sql, Double.class, params, 1);
        String code = db.getErrCode();
        this.setErrCode(code);
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
            ret = (double) -1;
        } else {
            if (ret == null || ret < 0) {
                ret = 0.0;
            }
        }
        return ret.doubleValue();
    }

    //to add new record to database by map
    public int addUserCharge(Map<String, Object> values) {
        //if (!values.containsKey("agentId")) {
        values.put("agentId", CloudContextUtils.getAgentId());
        //}
        int ret = db.insertRecord("user_charge", values);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to delete table record by id
    public int delUserCharge(int id) {
        String sql = "delete from user_charge where id=? and agentId=?";
        int ret = db.executeSql(sql, new Object[]{id, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to delete table record by cdt
    public int delUserCharge(Map<String, Object> cdt) {
        //if (!cdt.containsKey("agentId")) {
            cdt.put("agentId", CloudContextUtils.getAgentId());
        //}
        int ret = db.deleteRecord("user_charge", cdt);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to modify table record by values and condition
    public int modUserCharge(Map<String, Object> values, Map<String, Object> cdt) {
        //if (!cdt.containsKey("agentId")) {
        //    cdt.put("agentId", CloudContextUtils.getAgentId());
        //}
        int ret = db.modifyRecord("user_charge", values, cdt);
        String code = db.getErrCode();
        this.setErrCode(code);
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }

        return ret;
    }

    //to query user_charge item for detail
    public UserCharge queryUserChargeByOederId(String orderid) {
        String sql = "select * from user_charge where orderid = ?";
        UserCharge ret = db.queryForBean(sql, UserCharge.class, new Object[]{orderid});
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    public double queryUserChargeAmount(int userid) {
        return this.queryUserChargeAmount(userid, CloudContextUtils.getAgentId());
    }

    //to query user_charge item for detail
    public double queryUserChargeAmount(int userid, Integer agentId) {
        String sql = "select sum(amount) from user_charge where status='Y' and  userid=? and agentId=?";
        double total = (Double) db.queryFieldValue(sql, Float.class, new Object[]{userid, agentId}, 1);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        if (total < 0) {
            return 0;
        }
        return total;
    }

    //to query user_charge item for Amount
    public double queryChargeAmount(Map<String, Object> cdt) {
        //if (!cdt.containsKey("agentId")) {
            cdt.put("agentId", CloudContextUtils.getAgentId());
        //}
        List<Object> list = new ArrayList<>();
        StringBuilder sql = new StringBuilder("select  ifnull(sum(amount),0) from user_charge where 1=1 ");
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("start")) {
                sql.append(" and regTime >=?");
                list.add(c.getValue());
            } else if (c.getKey().equals("end")) {
                sql.append(" and regTime <?");
                list.add(c.getValue());
            } else {
                sql.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        Double total = (Double) db.queryFieldValue(sql.toString(), Float.class, params, 1);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        if (total < 0 || total == null) {
            return 0;
        }
        return total.doubleValue();
    }

    public List<Object[]> queryChargecount(Map<String, Object> cdt) {
        //if (!cdt.containsKey("agentId")) {
            cdt.put("agentId", CloudContextUtils.getAgentId());
        //}
        List<Object> list = new ArrayList<>();
        StringBuilder sql = new StringBuilder("select bankcode ,ifnull(sum(amount),0) from user_charge where status='Y'  ");
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("start")) {
                sql.append(" and regTime >=? ");
                list.add(c.getValue());
            } else if (c.getKey().equals("end")) {
                sql.append(" and regTime <? ");
                list.add(c.getValue());
            } else {
                sql.append(" and " + c.getKey() + "=? ");
                list.add(c.getValue());
            }
        }
        sql.append(" group by bankcode");
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<Object[]> count = db.queryForArrayList(sql.toString(), params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return count;
    }

    /**
     * 按时间段统计充值金额
     *
     * @param cdt
     * @param qryType H 小时 D 天 M 月 Y 年
     * @return add wzj by 2018/12/12
     */
    public List<Statistics> queryChargeCount(Map<String, Object> cdt, String qryType) {
        //if (!cdt.containsKey("agentId")) {
            cdt.put("agentId", CloudContextUtils.getAgentId());
        //}
        String format = StatistisTool.getSqlDateFormat(qryType);
        List<Object> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select ifnull(sum(amount),0) as count, DATE_FORMAT(regTime, '" + format + "') as dateTime from user_charge where status='Y' ");
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("startTime")) {
                sb.append(" and regTime >= ?");
                list.add(c.getValue());
            } else if (c.getKey().equals("endTime")) {
                sb.append(" and regTime <= ?");
                list.add(c.getValue());
            } else {
                sb.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }

        sb.append(" group by DATE_FORMAT(regTime,'" + format + "') ");
        sb.append(" order by regTime asc");
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<Statistics> rets = db.queryForBeans(sb.toString(), Statistics.class, params);
        this.setErrCode(db.getErrCode());
        return rets;
    }
}